
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 07/17/2012 10:24:39
-- Generated from EDMX file: D:\MyWork\InsightAuto\caca-insightauto\InsightAuto.Domain\Core\Core.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ArticleArticleCategory]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Articles] DROP CONSTRAINT [FK_ArticleArticleCategory];
GO
IF OBJECT_ID(N'[dbo].[FK_BrandSeries]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Series] DROP CONSTRAINT [FK_BrandSeries];
GO
IF OBJECT_ID(N'[dbo].[FK_SeriesModel]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Models] DROP CONSTRAINT [FK_SeriesModel];
GO
IF OBJECT_ID(N'[dbo].[FK_ModelModelDetail]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Models] DROP CONSTRAINT [FK_ModelModelDetail];
GO
IF OBJECT_ID(N'[dbo].[FK_ManufacturerBrand]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Brands] DROP CONSTRAINT [FK_ManufacturerBrand];
GO
IF OBJECT_ID(N'[dbo].[FK_DealerPrice]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Prices] DROP CONSTRAINT [FK_DealerPrice];
GO
IF OBJECT_ID(N'[dbo].[FK_ModelPrice]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Prices] DROP CONSTRAINT [FK_ModelPrice];
GO
IF OBJECT_ID(N'[dbo].[FK_SeriesClass]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Series] DROP CONSTRAINT [FK_SeriesClass];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Medias]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Medias];
GO
IF OBJECT_ID(N'[dbo].[AdvertisePositions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[AdvertisePositions];
GO
IF OBJECT_ID(N'[dbo].[Articles]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Articles];
GO
IF OBJECT_ID(N'[dbo].[ArticleCategories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ArticleCategories];
GO
IF OBJECT_ID(N'[dbo].[Brands]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Brands];
GO
IF OBJECT_ID(N'[dbo].[Series]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Series];
GO
IF OBJECT_ID(N'[dbo].[Models]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Models];
GO
IF OBJECT_ID(N'[dbo].[ModelDetails]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ModelDetails];
GO
IF OBJECT_ID(N'[dbo].[Manufacturers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Manufacturers];
GO
IF OBJECT_ID(N'[dbo].[Prices]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Prices];
GO
IF OBJECT_ID(N'[dbo].[Dealers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Dealers];
GO
IF OBJECT_ID(N'[dbo].[CarClasses]', 'U') IS NOT NULL
    DROP TABLE [dbo].[CarClasses];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Medias'
CREATE TABLE [dbo].[Medias] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Url] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'AdvertisePositions'
CREATE TABLE [dbo].[AdvertisePositions] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Remark] nvarchar(max)  NOT NULL,
    [PositionCode] nvarchar(max)  NOT NULL,
    [Content_Url] nvarchar(max)  NULL,
    [Content_Width] int  NOT NULL,
    [Content_Height] int  NOT NULL
);
GO

-- Creating table 'Articles'
CREATE TABLE [dbo].[Articles] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Content] nvarchar(max)  NOT NULL,
    [SubmitTime] datetime  NOT NULL,
    [Submitter] nvarchar(max)  NOT NULL,
    [Category_ID] int  NOT NULL
);
GO

-- Creating table 'ArticleCategories'
CREATE TABLE [dbo].[ArticleCategories] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Brands'
CREATE TABLE [dbo].[Brands] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Manufacturer_ID] int  NOT NULL
);
GO

-- Creating table 'Series'
CREATE TABLE [dbo].[Series] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Brand_ID] int  NOT NULL,
    [Class_ID] int  NOT NULL
);
GO

-- Creating table 'Models'
CREATE TABLE [dbo].[Models] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Year] int  NOT NULL,
    [IsRetired] bit  NOT NULL,
    [Series_ID] int  NOT NULL,
    [Detail_ID] int  NOT NULL
);
GO

-- Creating table 'ModelDetails'
CREATE TABLE [dbo].[ModelDetails] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Displacement] float  NULL,
    [LengthWidthHeight] nvarchar(max)  NULL,
    [WheelBase] int  NULL,
    [MaxPower] int  NULL,
    [FuelConsumption] float  NULL,
    [MaxSpeed] int  NULL,
    [Transmission] nvarchar(max)  NULL,
    [Torque] nvarchar(max)  NULL,
    [Weight] float  NULL,
    [Seats] int  NULL,
    [CargoVolume] float  NULL,
    [Doors] int  NULL,
    [TrackFront] float  NULL,
    [TrackRear] float  NULL,
    [ReachAngle] float  NULL,
    [LeaveAngle] float  NULL,
    [GroundClearance] float  NULL,
    [DisplacementDetail] float  NULL,
    [Charge] nvarchar(max)  NULL,
    [CompressionRatio] float  NULL,
    [ValveConfiguration] nvarchar(max)  NULL,
    [Engine] nvarchar(max)  NULL,
    [EngineStyle] nvarchar(max)  NULL,
    [Cylinders] int  NULL,
    [ValvesPerCylinder] int  NULL,
    [FrontSuspension] nvarchar(max)  NULL,
    [RearSuspension] nvarchar(max)  NULL,
    [FrontBrakes] nvarchar(max)  NULL,
    [RearBrakes] nvarchar(max)  NULL,
    [Drivetrain] nvarchar(max)  NULL,
    [SpareTire] nvarchar(max)  NULL,
    [KPH100AccelerateTime] float  NULL,
    [KPH100BrakeTime] float  NULL,
    [FrontTires] nvarchar(max)  NULL,
    [RearTires] nvarchar(max)  NULL,
    [FrontHub] nvarchar(max)  NULL,
    [RearHub] nvarchar(max)  NULL,
    [Windage] float  NULL
);
GO

-- Creating table 'Manufacturers'
CREATE TABLE [dbo].[Manufacturers] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'Prices'
CREATE TABLE [dbo].[Prices] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Value] float  NOT NULL,
    [Time] datetime  NOT NULL,
    [Dealer_ID] int  NOT NULL,
    [Model_ID] int  NOT NULL
);
GO

-- Creating table 'Dealers'
CREATE TABLE [dbo].[Dealers] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [Fax] nvarchar(max)  NOT NULL,
    [Address] nvarchar(max)  NOT NULL,
    [Phone] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'CarClasses'
CREATE TABLE [dbo].[CarClasses] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'Medias'
ALTER TABLE [dbo].[Medias]
ADD CONSTRAINT [PK_Medias]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'AdvertisePositions'
ALTER TABLE [dbo].[AdvertisePositions]
ADD CONSTRAINT [PK_AdvertisePositions]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Articles'
ALTER TABLE [dbo].[Articles]
ADD CONSTRAINT [PK_Articles]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ArticleCategories'
ALTER TABLE [dbo].[ArticleCategories]
ADD CONSTRAINT [PK_ArticleCategories]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Brands'
ALTER TABLE [dbo].[Brands]
ADD CONSTRAINT [PK_Brands]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Series'
ALTER TABLE [dbo].[Series]
ADD CONSTRAINT [PK_Series]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Models'
ALTER TABLE [dbo].[Models]
ADD CONSTRAINT [PK_Models]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'ModelDetails'
ALTER TABLE [dbo].[ModelDetails]
ADD CONSTRAINT [PK_ModelDetails]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Manufacturers'
ALTER TABLE [dbo].[Manufacturers]
ADD CONSTRAINT [PK_Manufacturers]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Prices'
ALTER TABLE [dbo].[Prices]
ADD CONSTRAINT [PK_Prices]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'Dealers'
ALTER TABLE [dbo].[Dealers]
ADD CONSTRAINT [PK_Dealers]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'CarClasses'
ALTER TABLE [dbo].[CarClasses]
ADD CONSTRAINT [PK_CarClasses]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Category_ID] in table 'Articles'
ALTER TABLE [dbo].[Articles]
ADD CONSTRAINT [FK_ArticleArticleCategory]
    FOREIGN KEY ([Category_ID])
    REFERENCES [dbo].[ArticleCategories]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ArticleArticleCategory'
CREATE INDEX [IX_FK_ArticleArticleCategory]
ON [dbo].[Articles]
    ([Category_ID]);
GO

-- Creating foreign key on [Brand_ID] in table 'Series'
ALTER TABLE [dbo].[Series]
ADD CONSTRAINT [FK_BrandSeries]
    FOREIGN KEY ([Brand_ID])
    REFERENCES [dbo].[Brands]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_BrandSeries'
CREATE INDEX [IX_FK_BrandSeries]
ON [dbo].[Series]
    ([Brand_ID]);
GO

-- Creating foreign key on [Series_ID] in table 'Models'
ALTER TABLE [dbo].[Models]
ADD CONSTRAINT [FK_SeriesModel]
    FOREIGN KEY ([Series_ID])
    REFERENCES [dbo].[Series]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SeriesModel'
CREATE INDEX [IX_FK_SeriesModel]
ON [dbo].[Models]
    ([Series_ID]);
GO

-- Creating foreign key on [Detail_ID] in table 'Models'
ALTER TABLE [dbo].[Models]
ADD CONSTRAINT [FK_ModelModelDetail]
    FOREIGN KEY ([Detail_ID])
    REFERENCES [dbo].[ModelDetails]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModelModelDetail'
CREATE INDEX [IX_FK_ModelModelDetail]
ON [dbo].[Models]
    ([Detail_ID]);
GO

-- Creating foreign key on [Manufacturer_ID] in table 'Brands'
ALTER TABLE [dbo].[Brands]
ADD CONSTRAINT [FK_ManufacturerBrand]
    FOREIGN KEY ([Manufacturer_ID])
    REFERENCES [dbo].[Manufacturers]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ManufacturerBrand'
CREATE INDEX [IX_FK_ManufacturerBrand]
ON [dbo].[Brands]
    ([Manufacturer_ID]);
GO

-- Creating foreign key on [Dealer_ID] in table 'Prices'
ALTER TABLE [dbo].[Prices]
ADD CONSTRAINT [FK_DealerPrice]
    FOREIGN KEY ([Dealer_ID])
    REFERENCES [dbo].[Dealers]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DealerPrice'
CREATE INDEX [IX_FK_DealerPrice]
ON [dbo].[Prices]
    ([Dealer_ID]);
GO

-- Creating foreign key on [Model_ID] in table 'Prices'
ALTER TABLE [dbo].[Prices]
ADD CONSTRAINT [FK_ModelPrice]
    FOREIGN KEY ([Model_ID])
    REFERENCES [dbo].[Models]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ModelPrice'
CREATE INDEX [IX_FK_ModelPrice]
ON [dbo].[Prices]
    ([Model_ID]);
GO

-- Creating foreign key on [Class_ID] in table 'Series'
ALTER TABLE [dbo].[Series]
ADD CONSTRAINT [FK_SeriesClass]
    FOREIGN KEY ([Class_ID])
    REFERENCES [dbo].[CarClasses]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SeriesClass'
CREATE INDEX [IX_FK_SeriesClass]
ON [dbo].[Series]
    ([Class_ID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------